#!/usr/bin/env python
# coding: utf-8

import logging
import xlrd
import xlwt
from xlutils.copy import copy


class ExcelUtils(object):
    """
    Excel 工具类
    """

    @classmethod
    def read_from_excel(cls, file_name, sheet_name):
        """
        从Excel里面读出数据
        :param file_name:
        :param sheet_name:
        :return:
        """
        try:
            wb = xlrd.open_workbook(file_name)
            ws = wb.sheet_by_name(sheet_name)
        except Exception as ex:
            logging.warning('open excel error：{0}'.format(ex))
            return

        nrows = ws.nrows
        ncols = ws.ncols
        field_key = dict()
        result = list()
        for nr in xrange(nrows):
            row_list = list()
            field_dict = dict()
            for nc in xrange(ncols):
                value = str(ws.cell(nr, nc).value).encode('utf-8', 'ignore')
                if not nr:  # get header of table
                    field_key[nc] = value
                else:
                    field_dict[field_key[nc]] = value
                if field_dict:
                    row_list.append(field_dict)
            if row_list:
                result.append(field_dict)
        return result

    @classmethod
    def _export_line_excel(cls, excel, sheet, fields=list()):
            """
            导出为Excel
            :param fields: 元数据(行级)
            :param excel: 保存的excel文件
            :param sheet: sheet名称
            :return:
            """

            try:
                excel_data = xlrd.open_workbook(excel)
                wb = copy(excel_data)
                try:
                    ws = wb.get_sheet(sheet)
                    table = excel_data.sheet_by_name(sheet)
                    nrow = table.nrows
                    for j in range(0, len(fields)):
                        ws.write(nrow, j, str(fields[j]).decode('utf-8').strip())
                except:
                    ws = wb.add_sheet(sheet)
                    for j in range(0, len(fields)):
                        ws.write(0, j, str(fields[j]).decode('utf-8').strip())
            except:
                wb = xlwt.Workbook()
                ws = wb.add_sheet(sheet)
                for j in range(0, len(fields)):
                    ws.write(0, j, str(fields[j]).decode('utf-8').strip())
            wb.save(excel)

    @classmethod
    def export_excel(cls, excel, sheet, content=list()):
        """
        导出为Excel
        :param content: 二维数组
        :param excel:
        :param sheet:
        :return:
        """
        for index, line in enumerate(content):
            if isinstance(line, list):
                cls._export_line_excel(excel, sheet, line)
            else:
                logging.error("The import data format is out of order, please checkout!")
                break

    @classmethod
    def txt_to_excel(cls, text, excel, sheet):
        """
        text文本转成excel
        :param text: text源文件
        :param excel: 保存的excel文件
        :param sheet: sheet名称
        :return:
        """

        data = open(text).read()
        lines = data.split('\n')
        wb = xlwt.Workbook()
        ws = wb.add_sheet(sheet)
        for i in range(0, len(lines)):
            fields = lines[i].split('\t')
            for j in range(0, len(fields)):
                ws.write(i, j, fields[j].decode('UTF-8').strip())
        wb.save(excel)
